﻿//  Key Tracker - An application to keep track of product keys.
//
//  Copyright (c) 2009  Shawn Rakowski
//
//  Permission is hereby granted, free of charge, to any person
//  obtaining a copy of this software and associated documentation
//  files (the "Software"), to deal in the Software without
//  restriction, including without limitation the rights to use,
//  copy, modify, merge, publish, distribute, sublicense, and/or sell
//  copies of the Software, and to permit persons to whom the
//  Software is furnished to do so, subject to the following
//  conditions:
//
//  The above copyright notice and this permission notice shall be
//  included in all copies or substantial portions of the Software.
//
//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
//  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
//  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
//  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
//  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
//  WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
//  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
//  OTHER DEALINGS IN THE SOFTWARE.
// --------------------------------------------------------------------------//
/*      KeyTrackerDatabase.cs 
 *      
 * 
 *      Key Tracker Project   
 *      
 *      CODE FILE DESCRIPTION:
 *      
 *          This code file contains the class contains all the properties and
 *          methods to work with key tracker database files. Which are database
 *          files implemented using SQLite for ADO.NET.
 * 
 *      AUTHOR(S):
 *      
 *          Shawn Rakowski
 * 
 *      CLASSES:
 *      
 *          KeyTrackerDatabse
 *      
 *      MODIFICATION HISTORY:
 *      
 *          06-AUG-2008     Shawn R.    New Program.
 *          10-AUG-2008     Shawn R.    Moved new/open dialogs to MainForm where they should be. 
 *                                      Added replicated database option.
 *                                      Added changesMade property.
 *                                      Added ExportAllKeys method.
 *          17-AUG-2008     Shawn R.    Added to OpenDB, now will run Alter DB Queue before checking
 *                                      the tables, allows for addition of new tables to old DB's. 
 *          16-MAR-2009     Shawn R.    Changed Add's to used SQLite Adapters (sql injection prevention).
 */

#region Using Statements

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;
using KeyTracker.Classes;

#endregion

namespace KeyTracker.Classes
{
    public enum IMPORT_FORMATS { }
    public enum EXPORT_FORMATS { TXT = 0, CSV = 1 }

    public class KeyTrackerDatabase
    {
        #region Class Level Variables

        private bool dbOpened;
        private bool useReplicatedDB;
        private bool usingReplicatedDB;
        private bool changesMade;
        private string fileName = "";
        private string originalFileName = "";
        private SQLiteConnection connection;
        SQLCommands sqlCommands = new SQLCommands();
        List<ProductRec> products = new List<ProductRec>();
        

        #endregion

        #region Constructors

        /// <summary>
        /// Contructs a new Key Tracker Database Class.
        /// </summary>
        public KeyTrackerDatabase()
        {
            dbOpened = false;
            useReplicatedDB = false;
            usingReplicatedDB = false;
            changesMade = false;
        }

        /// <summary>
        /// Contructs a new Key Tracker Database Class with option of using
        /// a replicated database.
        /// </summary>
        /// <param name="useReplicatedDatabase"></param>
        public KeyTrackerDatabase(bool useReplicatedDatabase)
        {
            dbOpened = false;
            useReplicatedDB = useReplicatedDatabase;
            usingReplicatedDB = false;
            changesMade = false;
        }

        #endregion

        #region Properties

        /// <summary>
        /// Returns true if database connection is open.
        /// </summary>
        public bool DBOpen
        {
            get { return dbOpened; }
        }

        /// <summary>
        /// Returns the file name of the database.
        /// </summary>
        public string DBFileName
        {
            get 
            {
                if (useReplicatedDB)
                    return originalFileName;
                else
                    return fileName; 
            }
        }

        /// <summary>
        /// Returns an object[] of all the products in the database that can
        /// be used to populate combo boxes, list boxes, or other controls.
        /// </summary>
        public object[] ProductRange
        {
            get
            {
                object[] 
                    productRange;

                PopulateProductList();
                productRange = new object[products.Count];
                for (int i = 0; i < products.Count; i++)
                {
                    productRange[i] = products[i].ProductName;
                }
                return productRange;
            }
        }

        /// <summary>
        /// Gets or sets whether Replicated Databases should be used.
        /// </summary>
        public bool UseReplicatedDatabase
        {
            set { useReplicatedDB = value; }
            get { return useReplicatedDB; }
        }

        /// <summary>
        /// Returns true if currenty using a replicated database.
        /// </summary>
        public bool UsingReplicatedDatabase
        {
            get { return usingReplicatedDB; }
        }

        /// <summary>
        /// Returns true if any changes have been made to the database.
        /// </summary>
        public bool ChangesHaveBeenMade
        {
            get { return changesMade; }
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Creates a new Key Tracker Database File and opens it.
        /// </summary>
        public void NewDB(string newFileName)
        {
            #region Variable Declarations
          
            SQLiteConnectionStringBuilder
                connectionString;

            SQLiteCommand
                buildTable;

            Queue<string>
                createTablesQueue;

            #endregion

            try
            {
                fileName = newFileName;
                if (fileName != "")
                {
                    #region Create New File and Open SQLite Connection

                    if (System.IO.File.Exists(fileName))
                    {
                        System.IO.File.Delete(fileName);
                    }
                    SQLiteConnection.CreateFile(fileName);

                    connectionString = new SQLiteConnectionStringBuilder();
                    connectionString.DataSource = fileName;
                    connection = new SQLiteConnection(connectionString.ConnectionString);
                    connection.Open();

                    #endregion

                    #region Execute Table Creation SQL Commands

                    createTablesQueue = sqlCommands.CreateTableQueue;
                    while (createTablesQueue.Count > 0)
                    {
                        buildTable = new SQLiteCommand(connection);
                        buildTable.CommandText = createTablesQueue.Dequeue();
                        buildTable.ExecuteNonQuery();
                    }

                    #endregion

                    #region Replicated Database Handling

                    if (useReplicatedDB)
                    {
                        connection.Close();
                        ReplicateDatabase();
                        connectionString = new SQLiteConnectionStringBuilder();
                        connectionString.DataSource = fileName;
                        connection = new SQLiteConnection(connectionString.ConnectionString);
                        connection.Open();
                    }

                    #endregion

                    dbOpened = true;
                }
            }
            catch
            {
                ErrorExit("trying to create a new database");
            }
        }

        /// <summary>
        /// Opens an existing Key Tracker Database File. Including
        /// executing open file dialog box.
        /// </summary>
        public void OpenDB(string openFileName)
        {
            #region Variable Declarations
            
            SQLiteConnectionStringBuilder
                connectionString;

            SQLiteCommand
                alterTables,
                testTables;

            Queue<string>
                alterTablesQueue,
                testTablesQueue;

            #endregion

            try
            {
                fileName = openFileName;

                if (fileName != "")
                {
                    #region Open SQLite Connection to File

                    connectionString = new SQLiteConnectionStringBuilder();
                    connectionString.DataSource = fileName;
                    connection = new SQLiteConnection(connectionString.ConnectionString);
                    try
                    {
                        connection.Open();

                        #region Alter and Test Tables

                        alterTablesQueue = sqlCommands.AlterTablesQueue;
                        testTablesQueue = sqlCommands.TestTablesQueue;
                        try
                        {
                            while (alterTablesQueue.Count > 0)
                            {
                                try
                                {
                                    alterTables = new SQLiteCommand(connection);
                                    alterTables.CommandText = alterTablesQueue.Dequeue();
                                    alterTables.ExecuteNonQuery();
                                }
                                catch { /* DO NOTHING ALREADY ALTERED OR BAD DB */ }
                            }

                            while (testTablesQueue.Count > 0)
                            {
                                testTables = new SQLiteCommand(connection);
                                testTables.CommandText = testTablesQueue.Dequeue();
                                testTables.ExecuteScalar();
                            }

                            #region Replicated Database Handling

                            if (useReplicatedDB)
                            {
                                connection.Close();
                                ReplicateDatabase();
                                connectionString = new SQLiteConnectionStringBuilder();
                                connectionString.DataSource = fileName;
                                connection = new SQLiteConnection(connectionString.ConnectionString);
                                connection.Open();
                            }

                            #endregion

                            dbOpened = true;
                        }
                        catch
                        {
                            MessageBox.Show("Database file is corrupt or incorrectly formatted! Database was not opened.",
                            "Error opening database", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            dbOpened = false;
                        }

                        #endregion


                    }
                    catch
                    {
                        MessageBox.Show("Database file is corrupt or incorrectly formatted! Database was not opened.",
                                        "Error opening database", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        dbOpened = false;
                    }

                    #endregion
                }
            }
            catch
            {
                ErrorExit("trying to open an existing database");
            }
        }

        /// <summary>
        /// Closes the open Key Tracker Database file.
        /// </summary>
        /// <param name="SaveChangesIfReplicated"></param>
        public void CloseDB(bool SaveChangesIfReplicated)
        {
            if (dbOpened)
            {
                connection.Close();
                dbOpened = false;
                products.Clear();
                RestoreReplicatedDatabase(SaveChangesIfReplicated);
                changesMade = false;
            }   
        }

        /// <summary>
        /// Gets keys by index from listbox/combobox/etc that has products loaded.
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public object[] GetKeys(int index)
        {
            #region Variable Declarations

            Queue<string> 
                keys = new Queue<string>();

            SQLiteDataReader 
                reader;

            SQLiteCommand
                sqlCommand;

            string
                commandText;

            object[] 
                keysReturnThis;

            #endregion

            try
            {
                if (dbOpened)
                {
                    commandText = "select key_value from key_rec where product_id = " +
                                  products[index].ProductId.ToString() + ";";
                    sqlCommand = new SQLiteCommand(connection);
                    sqlCommand.CommandText = commandText;
                    reader = sqlCommand.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            keys.Enqueue(reader.GetString(0));
                        }
                    }

                    int totalKeys = keys.Count;
                    keysReturnThis = new object[totalKeys];
                    for (int i = 0; i < totalKeys; i++)
                    {
                        keysReturnThis[i] = keys.Dequeue();
                    }
                }
                else
                {
                    keysReturnThis = new object[0];
                }
                return keysReturnThis;
            }
            catch
            {
                ErrorExit("trying to retrieve keys");
                return new object[0];
            }
        }

        /// <summary>
        /// Creates new key database record with productId at passed index.
        /// </summary>
        /// <param name="productIndex"></param>
        /// <param name="keyValue"></param>
        public void AddKey(int productIndex, string keyValue)
        {
            try
            {
                if (dbOpened)
                {
                    SQLiteTransaction 
                        trans = connection.BeginTransaction();

                    SQLiteDataAdapter 
                        adapter = new SQLiteDataAdapter();

                    adapter.InsertCommand = new SQLiteCommand("INSERT INTO key_rec(product_id, key_value) " +
                                            "VALUES (@index, @value);"); 
                    adapter.InsertCommand.Parameters.AddWithValue("@index", products[productIndex].ProductId.ToString());
                    adapter.InsertCommand.Parameters.AddWithValue("@value", keyValue);
                    adapter.InsertCommand.Transaction = trans;
                    adapter.InsertCommand.ExecuteNonQuery();
                    changesMade = true;
                    trans.Commit();
                }
            }
            catch
            {
                ErrorExit("trying to add a key to the database");
            }
        }

        /// <summary>
        /// Deletes all key database records with productId at passed index and keyValue.
        /// </summary>
        /// <param name="productIndex"></param>
        /// <param name="keyValue"></param>
        public void DeleteKey(int productIndex, string keyValue)
        {
            #region Variable Declarations

            SQLiteCommand
                sqlCommand;

            string
                commandText;

            #endregion

            try
            {
                if (dbOpened)
                {
                    commandText = "DELETE FROM key_rec WHERE product_id = " +
                                  products[productIndex].ProductId.ToString() +
                                  " AND key_value = '" +
                                  keyValue.Replace("'", "''") + "';";
                    sqlCommand = new SQLiteCommand(connection);
                    sqlCommand.CommandText = commandText;
                    sqlCommand.ExecuteNonQuery();
                    changesMade = true;
                }
            }
            catch
            {
                ErrorExit("trying to delete a key from the database");
            }
        }

        /// <summary>
        /// Adds a product with the given product name.
        /// </summary>
        /// <param name="productName"></param>
        public void AddProduct(string productName)
        {
            try
            {
                if (dbOpened)
                {
                    SQLiteTransaction
                        trans = connection.BeginTransaction();

                    SQLiteDataAdapter 
                        adapter = new SQLiteDataAdapter();

                    adapter.InsertCommand = new SQLiteCommand("INSERT INTO product_rec(product_name) " +
                                            "VALUES (@product);");
                    adapter.InsertCommand.Parameters.AddWithValue("@product", productName);
                    adapter.InsertCommand.Transaction = trans;
                    adapter.InsertCommand.ExecuteNonQuery();
                    trans.Commit();

                    PopulateProductList();
                    changesMade = true;
                }
            }
            catch
            {
                ErrorExit("trying to add product to the database");
            }
        }

        /// <summary>
        /// Deletes all products with the passed Product Name. It also deletes ALL keys associated
        /// with its product id.
        /// </summary>
        /// <param name="productName"></param>
        public void DeleteProduct(int productIndex)
        {
            #region Variable Declarations

            SQLiteCommand
                sqlCommand;

            string
                commandText;

            #endregion

            try
            {
                if (dbOpened)
                {
                    // Delete all keys.
                    commandText = "DELETE FROM key_rec WHERE product_id = " +
                                  products[productIndex].ProductId.ToString() + ";";
                    sqlCommand = new SQLiteCommand(connection);
                    sqlCommand.CommandText = commandText;
                    sqlCommand.ExecuteNonQuery();

                    // Delete product.
                    commandText = "DELETE FROM product_rec WHERE product_id = " +
                                  products[productIndex].ProductId.ToString() + ";";
                    sqlCommand.CommandText = commandText;
                    sqlCommand.ExecuteNonQuery();
                    changesMade = true;

                    PopulateProductList();
                }
            }
            catch
            {
                ErrorExit("trying to delete a product from the database");
            }
        }

        /// <summary>
        /// Exports all keys to the passed file name in the passed format. For formats
        /// use the ExportFormat enumeration.
        /// </summary>
        /// <param name="outFileName"></param>
        /// <param name="exportFormat"></param>
        public void ExportAllKeys(string outFileNameAndPath, int exportFormat)
        {
            #region Variable Declarations

            System.IO.TextWriter
                fileOut;

            #endregion

            if (dbOpened)
            {
                if (System.IO.File.Exists(outFileNameAndPath))
                    System.IO.File.Delete(outFileNameAndPath);

                fileOut = new System.IO.StreamWriter(outFileNameAndPath);
                switch (exportFormat)
                {
                    case (int)EXPORT_FORMATS.CSV:
                        ExportToCsv(fileOut);
                        break;
                    case (int)EXPORT_FORMATS.TXT:
                    default:
                        ExportToTxt(fileOut);
                        break;
                }
                fileOut.Close();
            }
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Populates the product list variable with all products in the database.
        /// </summary>
        private void PopulateProductList()
        {
            #region Variable Declarations

            SQLiteCommand
                command;

            SQLiteDataReader
                reader;

            ProductRec
                product;
            
            #endregion

            try
            {
                products.Clear();
                if (dbOpened)
                {
                    command = new SQLiteCommand(connection);
                    command.CommandText = "select * from product_rec order by product_name;";
                    reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            product = new ProductRec(reader.GetInt32((int)PRODUCT_COLUMNS.PRODUCT_ID),
                                                     reader.GetString((int)PRODUCT_COLUMNS.PRODUCT_NAME));
                            products.Add(product);
                        }
                    }
                    reader.Close();
                }
            }
            catch
            {
                ErrorExit("trying to populate products list");
            }
        }

        /// <summary>
        /// Replicates existing database and stores it in a local folder. Sets
        /// fileName to replicated database name, and sets originalFileName to
        /// the fileName passed by the user.
        /// </summary>
        private void ReplicateDatabase()
        {
            #region Variable Declarations

            string
                tempFileName;

            #endregion

            if (useReplicatedDB && !dbOpened)
            {
                try
                {
                    tempFileName = DateTime.Now.ToString();
                    tempFileName = tempFileName.Replace(' ', '_');
                    tempFileName = tempFileName.Replace('/', '_');
                    tempFileName = tempFileName.Replace(':', '_');
                    tempFileName = "./tempDB_" + tempFileName;

                    originalFileName = fileName;
                    fileName = tempFileName;
                    System.IO.File.Copy(originalFileName, fileName);
                    usingReplicatedDB = true;
                }
                catch
                {
                    ErrorExit("trying to replicate database");
                }
            }
        }

        /// <summary>
        /// Copies replicated database to originalFileName location. Basically
        /// commits changes to the actual database.
        /// </summary>
        private void RestoreReplicatedDatabase(bool restoreFile)
        {
            if (!dbOpened && usingReplicatedDB)
            {
                try
                {
                    if (restoreFile) 
                    {
                        System.IO.File.Delete(originalFileName);
                        System.IO.File.Copy(fileName, originalFileName);
                    }
                    System.IO.File.Delete(fileName);
                    usingReplicatedDB = false;
                }
                catch
                {
                    ErrorExit("trying to restore replicated database");
                }
            }
        }

        /// <summary>
        /// Diplays an error message box and closes the application.
        /// </summary>
        /// <param name="whatWasExecuting"></param>
        private void ErrorExit(string erroredWhile)
        {
            string 
                message = "Key Tracker encountered and unexpected error while " +
                             erroredWhile + " and needs to shut down. Sorry for the inconvenience.";
            MessageBox.Show(message, "Key Tracker Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            CloseDBErr();
            Application.Exit();
        }

        /// <summary>
        /// Close used for ErrorExit that does not call RestoreReplicatedDatabase
        /// prevents infinitely recursive loop.
        /// </summary>
        private void CloseDBErr()
        {
            if (dbOpened)
            {
                connection.Close();
                dbOpened = false;
                changesMade = false;
                products.Clear();
            }
        }
        
        #endregion

        #region Private Export Methods

        /// <summary>
        /// Writes all keys to the fileOut TextWriter passed in TXT format. 
        /// </summary>
        /// <param name="fileOut"></param>
        private void ExportToTxt(System.IO.TextWriter fileOut)
        {
            #region Variable Declarations

            object[]
                keys;

            #endregion

            fileOut.WriteLine("Key Tracker Product Key Report" + fileOut.NewLine);
            fileOut.WriteLine("Date: " + DateTime.Now.ToString() + fileOut.NewLine);
            fileOut.WriteLine("================================================================================");
            for (int i = 0; i < products.Count; i++)
            {
                fileOut.WriteLine(fileOut.NewLine);
                fileOut.WriteLine(products[i].ProductName.ToString() + ":");
                fileOut.WriteLine("------------------------------------------------------------");
                keys = GetKeys(i);
                foreach (object key in keys)
                {
                    fileOut.WriteLine((string)key + fileOut.NewLine);
                }
            }
            fileOut.WriteLine(fileOut.NewLine);
            fileOut.WriteLine("================================================================================");
        }

        /// <summary>
        /// Writes all keys to the fileOut TextWriter passed in CSV format. 
        /// </summary>
        /// <param name="fileOut"></param>
        private void ExportToCsv(System.IO.TextWriter fileOut)
        {
            #region Variable Declarations

            object[]
                keys;

            #endregion

            fileOut.WriteLine("Key Tracker Product Key Report" + fileOut.NewLine);
            fileOut.WriteLine("Date:," + DateTime.Now.ToString() + fileOut.NewLine);
            for (int i = 0; i < products.Count; i++)
            {
                keys = GetKeys(i);
                foreach (object key in keys)
                {
                    fileOut.WriteLine(products[i].ProductName.ToString() + 
                                      "," + (string)key);
                }
            }
        }

        #endregion
    }
}